/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package supervision.report;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;
import javax.naming.NamingException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import system.general.Exc;
import system.general.Logged;
import system.general.Message;
import system.general.Persistencia;

/**
 *
 * @author emojica
 */
@ManagedBean(name = "supervision_administrativeReport")
@RequestScoped
public class Administrative {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Message msg = new Message();
    private ResultSet rsts;
    private String sql;
    public String detailTable;
    public String headerTable;
    public String reportName;
    private supervision.administrative.Find administrative_Find = new supervision.administrative.Find();

    public Administrative() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
        detailTable = "reportes.supervision_administrativa_detalle_" + String.valueOf(logged.getLoggedId());
        headerTable = "reportes.supervision_administrativa_header_" + String.valueOf(logged.getLoggedId());
        reportName = "supervision_administrativa.jasper";
    }

    public void report() throws Exc, SQLException, NamingException, Exception {
        try {
            Map params = new HashMap();
            system.general.Path path = new system.general.Path();

            //Elimina la tabla
            dropTable();

            //Crea la tabla 
            createTable();

            //inserta los datos
            loadData();

            //lee las propiedades y carga el mapa
            params.put("detailTable", detailTable);
            params.put("headerTable", headerTable);
            params.put("SUBREPORT_DIR", path.getSupervisionReportPath());
            params.put("logoPath", path.getLogoPathView());
            params.put("reportPath", path.getReportPathView());
            String jasperReport = path.getSupervisionReportPath() + reportName;
            this.persistencia.dbConnect();
            createReport(jasperReport, params, persistencia.getConn());
        } catch (JRException ex) {
        } catch (Exception ex) {
        } finally {
            this.persistencia.dbDisconnect();
        }
    }

    public Properties leerProperties() {
        Properties propiedades = new Properties();
        try {
            propiedades.load(getClass().getResourceAsStream("/supervision/administrative/label_" + logged.getLoggedIdiom() + ".properties"));
            if (propiedades.isEmpty()) {
                return null;
            }
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
            return propiedades;
        }
    }

    public Properties leerReportProperties() {
        Properties propiedades = new Properties();
        try {
            propiedades.load(getClass().getResourceAsStream("label_" + logged.getLoggedIdiom() + ".properties"));
            if (propiedades.isEmpty()) {
                return null;
            }
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
            return propiedades;
        }
    }

    private void loadData() throws Exc, SQLException, NamingException, Exception {
        try {
            int preguntaNumero, numeroSolicitud;
            String descripcion_pregunta;
            String descripcion_respuesta;
            String universidad_nombre;
            String universidad_sede;
            String observacion = "";
            Date fecha_supervision = new Date();
            boolean checked;
            Map map = new LinkedHashMap();

            String select = "SELECT sa.id_solicitud as solicitud_sede, sa.observacion as observacion, se.id_solicitud as solicitud,"
                    + " ug.nombre as universidad_nombre, us.nombre as universidad_sede, sg.recibido as fecha"
                    + " FROM public.v_supervision_administrativa as sa"
                    + " INNER JOIN public.v_solicitud_sede se"
                    + " ON (sa.id_solicitud= se.id)"
                    + " LEFT JOIN solicitud_general sg"
                    + " ON (se.id_solicitud = sg.id)"
                    + " INNER JOIN public.v_universidad_sede us"
                    + " ON (se.id_sede = us.id)"
                    + " INNER JOIN public.v_universidad_general ug"
                    + " ON (us.id_universidad = ug.id)"
                    + " WHERE sa.id_solicitud=" + logged.getMasterId() + " and idl_pregunta=0";

            rsts = persistencia.executeRsts(select);

            rsts.next();

            universidad_nombre = rsts.getString("universidad_nombre");
            universidad_sede = rsts.getString("universidad_sede");
            numeroSolicitud = rsts.getInt("solicitud");
            fecha_supervision = rsts.getDate("fecha");
            observacion = rsts.getString("observacion");

            rsts.close();

            Properties mp = leerReportProperties();

            sql = "INSERT INTO " + headerTable + " "
                    + "(etiqueta1, detalle1,"
                    + " etiqueta2, detalle2,"
                    + " etiqueta3, detalle3,"
                    + " etiqueta4, detalle4,"
                    + " etiqueta5, detalle5)"
                    + " VALUES ("
                    + "'" + mp.getProperty("field1") + "','" + numeroSolicitud + "',"
                    + "'" + mp.getProperty("field2") + "','" + universidad_nombre + "',"
                    + "'" + mp.getProperty("field3") + "','" + universidad_sede + "',"
                    + "'" + mp.getProperty("field4") + "','" + fecha_supervision + "',"
                    + "'" + mp.getProperty("title1") + "','')";
            persistencia.executeUpdate(sql);

            mp.clear();
            mp = leerProperties();

            for (Enumeration idl_pregunta = mp.keys(); idl_pregunta.hasMoreElements();) {
                Object obj = idl_pregunta.nextElement();
                String questionKey = obj.toString();
                if (questionKey.startsWith("pregunta")) {
                    String codigo = questionKey.substring(8);
                    descripcion_pregunta = mp.getProperty(questionKey);
                    preguntaNumero = Integer.parseInt(codigo);
                    map.clear();
                    map.put("id_solicitud = " + logged.getMasterId(), " AND ");
                    map.put("idl_pregunta = " + preguntaNumero, " ");

                    checked = administrative_Find.byExist(map);

                    descripcion_respuesta = mp.getProperty("respuesta0");

                    if (checked) {
                        descripcion_respuesta = mp.getProperty("respuesta1");
                    }

                    sql = "INSERT INTO " + detailTable + " "
                            + "(idl_pregunta, "
                            + "descripcion_pregunta, "
                            + "descripcion_respuesta, "
                            + "observacion, "
                            + "checked) "
                            + "VALUES ("
                            + preguntaNumero + ","
                            + "'" + descripcion_pregunta + "',"
                            + "'" + descripcion_respuesta + "',"
                            + "'" + observacion + "',"
                            + checked + ")";
                    persistencia.executeUpdate(sql);
                }
            }

        } finally {
        }
    }

    private void dropTable() throws Exc, SQLException, NamingException, Exception {
        sql = "DROP TABLE IF EXISTS " + headerTable + "," + detailTable;

        persistencia.executeUpdate(sql);
    }

    private void createTable() throws Exc, SQLException, NamingException, Exception {
        sql = "CREATE TABLE " + detailTable
                + " (idl_pregunta integer, "
                + "descripcion_pregunta text,"
                + "descripcion_respuesta text,"
                + "observacion text, "
                + "checked boolean) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + detailTable
                + " OWNER TO core;";

        persistencia.executeUpdate(sql);

        sql = "CREATE TABLE " + headerTable
                + " (etiqueta1 text,"
                + "detalle1 text, "
                + "etiqueta2 text, "
                + "detalle2 text, "
                + "etiqueta3 text, "
                + "detalle3 text, "
                + "etiqueta4 text, "
                + "detalle4 text, "
                + "etiqueta5 text, "
                + "detalle5 text) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + headerTable
                + " OWNER TO core;";

        persistencia.executeUpdate(sql);
    }

    public void createReport(String archivoJasper, Map params, Connection ds) throws JRException, IOException {
        JasperPrint jasperPrint = JasperFillManager.fillReport(archivoJasper, params, ds);
        HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
        JasperExportManager.exportReportToPdfStream(jasperPrint, servletOutputStream);
    }

    public String getTableName() {
        return detailTable;
    }

    private String namePackage() {
        Package pack = this.getClass().getPackage();
        return pack.getName();
    }

    public boolean exist() {
        boolean exist = false;
        Map map = new LinkedHashMap();

        map.clear();

        map.put("id_solicitud = " + logged.getMasterId(), "");
        try {
            exist = administrative_Find.byExist(map);

        } catch (Exception ex) {
        } finally {
        }
        return exist;
    }
}
